package com.friday.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.friday.dao.EmployeeDao;
import com.friday.po.Department;
import com.friday.po.Employee;
import com.friday.util.DBUtil;

public class EmployeeDaoImpl implements EmployeeDao {
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pStmt = null;
	private ResultSet rs = null;
	private DBUtil util = null;
	
	@Override
	public int insertEmployee(Employee emp) throws Exception {
		String sql = "insert into employee values(?, ?, ?, ?, ?, ?, ?)";
		int ret;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, emp.getId());
			pStmt.setString(2, emp.getName());
			pStmt.setString(3, emp.getAccount());
			pStmt.setString(4, emp.getPassword());
			pStmt.setInt(5, emp.getActor());
			pStmt.setInt(6, emp.getState());
			pStmt.setString(7, emp.getDepartment());
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public int deleteEmployeeById(String id) throws Exception {
		String sql = "delete from employee where id = ?";
		int ret = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, id);
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public int updateEmployee(Employee emp) throws Exception {
		String sql = "update employee set name = ?, password = ?, actor = ?, state = ?, department = ? where id =?";
		int ret;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, emp.getName());
			pStmt.setString(2, emp.getPassword());
			pStmt.setInt(3, emp.getActor());
			pStmt.setInt(4, emp.getState());
			pStmt.setString(5, emp.getDepartment());
			pStmt.setString(6, emp.getId());
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public List<Employee> queryAllEmployee() throws Exception {
		String sql = "select * from employee";
		List<Employee> list = new ArrayList<Employee>();
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				String id = rs.getString("id");
				String name = rs.getString("name");
				String account = rs.getString("account");
				String password = rs.getString("password");
				int actor = rs.getInt("actor");
				int state = rs.getInt("state");
				String department = rs.getString("department");
 				Employee emp = new Employee(id, name, account, password, actor, state, department);
				list.add(emp);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(rs);
			util.close(stmt);
			util.close(conn);
		}
		
		return list;
	}

	@Override
	public Employee queryEmployeeById(String id) throws Exception {
		String sql = "select * from employee where id = ?";
		Employee emp = null;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, id);
			rs = pStmt.executeQuery();
			while(rs.next()){
				String name = rs.getString("name");
				String account = rs.getString("account");
				String password = rs.getString("password");
				int actor = rs.getInt("actor");
				int state = rs.getInt("state");
				String department = rs.getString("department");
 				emp = new Employee(id, name, account, password, actor, state, department);
 				break;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return emp;
	}

	@Override
	public int deleteEmployeeByAccount(String account) throws Exception {
		String sql = "delete from employee where account = ?";
		int ret = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, account);
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public List<Employee> queryEmployeesByDepartmentId(String depId) throws Exception {
		String sql = "select * from employee where department = ?";
		List<Employee> list = new ArrayList<Employee>();
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, depId);
			rs = pStmt.executeQuery();
			while(rs.next()){
				String id = rs.getString("id");
				String name = rs.getString("name");
				String account = rs.getString("account");
				String password = rs.getString("password");
				int actor = rs.getInt("actor");
				int state = rs.getInt("state");
 				Employee emp = new Employee(id, name, account, password, actor, state, depId);
				list.add(emp);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(rs);
			util.close(pStmt);
			util.close(conn);
		}
		
		return list;
	}

	@Override
	public Employee queryEmployeesByAccount(String account) throws Exception {
		String sql = "select * from employee where account = ?";
		Employee emp = null;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, account);
			rs = pStmt.executeQuery();
			while(rs.next()){
				String id = rs.getString("id");
				String name = rs.getString("name");
				String password = rs.getString("password");
				int actor = rs.getInt("actor");
				int state = rs.getInt("state");
				String department = rs.getString("department");
 				emp = new Employee(id, name, account, password, actor, state, department);
 				break;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return emp;
	}
}